package modelosqlserver;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.SwingUtilities;

import transacciones.ConeccionDAO;
import transacciones.TransactionDelegate;
import utilitario.FuncionesUsuario;

public class AreaDAO extends ConeccionDAO {

	private Area are;
	private FuncionesUsuario funUsu;
	private FileInputStream fis;

	public AreaDAO(Area parAre, TransactionDelegate parTransactionDelegate, FuncionesUsuario funUsu) {

		super(parTransactionDelegate);
		this.are = parAre;
		this.funUsu = funUsu;

	}

	public int agregar(Area parAre) {

		String conSql;
		int codIns = 0;
		PreparedStatement pstm;
		ResultSet resSql;
		try {

			conSql = "insert into area values(?,?,?,?,?,?,?)";

			pstm = obtenerConeccion().prepareStatement(conSql, Statement.RETURN_GENERATED_KEYS);

			// pstm.setInt(1, parAre.getAre_cod());
			pstm.setString(1, parAre.getAre_ide());
			pstm.setString(2, parAre.getAre_nom());
			pstm.setString(3, parAre.getAre_des());
			pstm.setString(4, parAre.getAre_bar());
			pstm.setBoolean(5, parAre.isAre_act());
			pstm.setInt(6, parAre.getAre_ord());
			pstm.setInt(7, parAre.getAre_ver());

			if (pstm.executeUpdate() == 1) {
				resSql = pstm.getGeneratedKeys();
				while (resSql.next()) {

					codIns = resSql.getInt(1);

				}
				resSql.close();

			}// Fin de if(pstm.executeUpdate() == 1)

			pstm.close();
			return codIns;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente

	public int actualizar(Area parAre) {

		String conSql;
		int resCod = 0;
		PreparedStatement pstm;
		try {

			conSql = "update area set are_ide=?,are_nom=?,are_des=?,are_bar=?,are_act=?,are_ord=?,are_ver=? where are_cod=? and are_ver=?";
			pstm = obtenerConeccion().prepareStatement(conSql);

			pstm.setString(1, parAre.getAre_ide());
			pstm.setString(2, parAre.getAre_nom());
			pstm.setString(3, parAre.getAre_des());
			pstm.setString(4, parAre.getAre_bar());
			pstm.setBoolean(5, parAre.isAre_act());
			pstm.setInt(6, parAre.getAre_ord());
			pstm.setInt(7, parAre.getAre_ver() + 1);
			pstm.setInt(8, parAre.getAre_cod());
			pstm.setInt(9, parAre.getAre_ver());

			if (pstm.executeUpdate() == 1) {

				resCod = parAre.getAre_cod();

			}// Fin de if(pstm.executeUpdate() == 1)

			pstm.close();
			return resCod;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente

	public Area obtenerArea(int parAreCod) {
		String conSql;
		ResultSet resSql;
		try {

			conSql = "select * from area where are_cod=" + parAreCod;
			resSql = obtenerConeccion().createStatement().executeQuery(conSql);

			are.limpiarInstancia();

			if (resSql.next()) {

				are.setAre_cod(resSql.getInt("are_cod"));
				are.setAre_ide(resSql.getString("are_ide"));
				are.setAre_nom(resSql.getString("are_nom"));
				are.setAre_des(resSql.getString("are_des"));
				are.setAre_bar(resSql.getString("are_bar"));
				are.setAre_act(resSql.getBoolean("are_act"));
				are.setAre_ord(resSql.getInt("are_ord"));
				are.setAre_ver(resSql.getInt("are_ver"));

			}// Fin de

			resSql.close();
			return are;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo getListaContacto()", this.getClass().getName());
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error
	}

	public Area obtenerArea(String parAreNom) {
		String conSql;
		ResultSet resSql;
		try {

			conSql = "select * from area where are_nom='" + parAreNom + "'";
			resSql = obtenerConeccion().createStatement().executeQuery(conSql);

			are.limpiarInstancia();

			if (resSql.next()) {

				are.setAre_cod(resSql.getInt("are_cod"));
				are.setAre_ide(resSql.getString("are_ide"));
				are.setAre_nom(resSql.getString("are_nom"));
				are.setAre_des(resSql.getString("are_des"));
				are.setAre_bar(resSql.getString("are_bar"));
				are.setAre_act(resSql.getBoolean("are_act"));
				are.setAre_ord(resSql.getInt("are_ord"));
				are.setAre_ver(resSql.getInt("are_ver"));

			}// Fin de

			resSql.close();
			return are;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo getListaContacto()", this.getClass().getName());
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error
	}

	public List<Area> obtenerListaArea(String parAreNom) {

		String conSql;
		if (parAreNom.equals("TODOS")) {

			conSql = "select * from area order by are_ord";

		}// Fin de if (parEmpNom.equals("TODOS"))
		else {

			conSql = "select * from area where "
					+ "(are_nom like '" + parAreNom + "%' or "
					+ "are_nom like '%" + parAreNom + "%') order by are_ord";

		}

		return obtenerListaEntidad(conSql);

	}// Fin de metodo obtenerListaDetPedido(int parPedCod)

	public List<Area> obtenerListaArea() {

		String conSql = "select * from area order by are_nom asc";
		return obtenerListaEntidad(conSql);

	}// Fin de metodo obtenerListaDetPedido(int parPedCod)

	public List<Area> obtenerListaEntidad(String parSql) {
		ResultSet resSql;
		try {

			resSql = obtenerConeccion().createStatement().executeQuery(parSql);
			List<Area> lisAre = new ArrayList<Area>();

			Area varAre;

			while (resSql.next()) {

				varAre = new Area();

				varAre.setAre_cod(resSql.getInt("are_cod"));
				varAre.setAre_ide(resSql.getString("are_ide"));
				varAre.setAre_nom(resSql.getString("are_nom"));
				varAre.setAre_des(resSql.getString("are_des"));
				varAre.setAre_bar(resSql.getString("are_bar"));
				varAre.setAre_act(resSql.getBoolean("are_act"));
				varAre.setAre_ord(resSql.getInt("are_ord"));
				varAre.setAre_ver(resSql.getInt("are_ver"));

				lisAre.add(varAre);

			}// Fin de

			resSql.close();
			return lisAre;

		} catch (SQLException e) {
			// funUsu.mostrarMensaje("¡¡¡Error en metodo getListaContacto()", this.getClass().getName());
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error

	}

	public boolean eliminar(int parAreCod) {
		String conSql;
		PreparedStatement pstm;
		try {

			conSql = "delete from area where are_cod=" + parAreCod;
			pstm = obtenerConeccion().prepareStatement(conSql);

			int afectadas = pstm.executeUpdate();
			return (afectadas == 1) ? true : false;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}// Fin de Metodo Agregar Docente

	public FileInputStream obtenerFileInputStream(File parFile) {
		try {
			fis = new FileInputStream(parFile);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fis;
	}

	public boolean existeRegistroZona(String parSql) {

		ResultSet resSql;
		try {

			resSql = obtenerConeccion().prepareStatement(parSql).executeQuery();

			if (resSql.next()) {

				return true;

			}

			return false;

		} catch (SQLException e) {

			e.printStackTrace();
			throw new RuntimeException(e);

		}// Fin Catch en caso de que haya un error

	}// Fin de metodo para leer un registro de producto

	public static void main(String[] args) {
		SwingUtilities.invokeLater(new Runnable() {
			@Override
			public void run() {
			}// Fin de public void run()
		});
	}// Fin de metod main
}// in de clase prinicipal